<?php
if(!defined('IN_BSTO')){ die('[ERROR] You cannot load this page directly !!!'); }
Class BookManager
{
	private $db = null;

	function __construct($db)
	{
		$this->db = $db;
	}
    
	public function getBookByUser($id, $type)
	{
		if ($type == 'Normal'){
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
					  FROM book AS b, entity AS e, category AS c, USER AS u 
					  WHERE u.uid = $id  AND u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' ";
		} else {
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
					  FROM book AS b, entity AS e, category AS c, USER AS u 	
					  WHERE u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' ";
		}
			
		//$this->db->startConnection();
		$tblResult = $this->db->getMixedData($query);
		//$this->db->closeConnection();
			
		return $tblResult;
			
	}
	public function getPlugin($nid){
		$query = "SELECT MAX(`like`) AS `like`, MAX(`share`) AS `share`, MAX(retweet) AS `retweet` FROM plugin WHERE nid = $nid";
		$result = $this->db->getMixedData($query);
		$row = $result[0];
		return $row;
	}
	public function getBookByUserFilter($id, $type)
	{
		$num = $this->getBookByNewDate($id);
		if ($type == 'Normal'){
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
    				  FROM book AS b, entity AS e, category AS c, USER AS u 
    				  WHERE u.uid = $id  AND u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' AND e.nid != $num";
		} else {
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
    				  FROM book AS b, entity AS e, category AS c, USER AS u 	
    				  WHERE u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' AND e.nid != $num";
		}
		 
		//$this->db->startConnection();
		$tblResult = $this->db->getMixedData($query);
		//$this->db->closeConnection();
		 
		return $tblResult;
		 
	}

	public function getBookByUserFilter2($id, $type)
	{
		$num = $this->getBookByNewDate2($id);
		if ($type == 'Normal'){
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
    				  FROM book AS b, entity AS e, category AS c, USER AS u 
    				  WHERE u.uid = $id  AND u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' AND e.nid != $num";
		} else {
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created
    				  FROM book AS b, entity AS e, category AS c, USER AS u 	
    				  WHERE u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' AND e.nid != $num";
		}
		 
		//$this->db->startConnection();
		$tblResult = $this->db->getMixedData($query);
		//$this->db->closeConnection();
		 
		return $tblResult;
		 
	}

	public function getBookByUserLimit($id, $type, $fst, $num)
	{
		if ($type == 'Normal'){
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created, b.feature, b.special
					  FROM book AS b, entity AS e, category AS c, USER AS u 
					  WHERE u.uid = $id  AND u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book' 
					  LIMIT $fst , $num";
		} else {
			$query = "SELECT b.nid, e.title, c.name, u.username, e.created, b.feature, b.special
					  FROM book AS b, entity AS e, category AS c, USER AS u 
					  WHERE u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND e.type = 'book'
					  LIMIT $fst ,$num";
		}
			
		//$this->db->startConnection();
		$tblResult = $this->db->getMixedData($query);
		//$this->db->closeConnection();
			
		return $tblResult;
			
	}


	public function delBookById($id)
	{
		if ($this->db != null) {
			//$this->db->startConnection();
			$this->db->dbQuery("DELETE FROM book WHERE nid = $id");
			$this->db->dbQuery("DELETE FROM entity WHERE nid = $id");
			//$this->db->closeConnection();
		}
	}

	public function setDelBookById($id)
	{
		if ($this->db != null) {
			//$this->db->startConnection();
			$this->db->dbQuery("UPDATE entity SET STATUS = 0 WHERE nid = $id");

			//$this->db->closeConnection();
		}
	}

	public function setSpecialById($id)
	{
		if ($this->db != null) {
			//$this->db->startConnection();
			$result = $this->db->dbQuery("SELECT special FROM book WHERE nid = " .$id.";");
			$row = mysql_fetch_assoc($result);
			if ($row['special'] == 1){
				$query = "UPDATE book SET special = 0 WHERE nid = " . $id . ";";
			} else {
				$query = "UPDATE book SET special = 1 WHERE nid = " . $id . ";" ;
			}
			$this->db->dbQuery($query);
            $query2 = "UPDATE entity SET entity.changed = NOW() WHERE nid = $id";
            $this->db->dbQuery($query2);
            
			//$this->db->closeConnection();
		}
	}

	public function setFeatureById($id)
	{
		if ($this->db != null) {
			//$this->db->startConnection();
			$result = $this->db->dbQuery("SELECT feature FROM book WHERE nid = " .$id.";");
			$row = mysql_fetch_assoc($result);
			if ($row['feature'] == 1){
				$query = "UPDATE book SET feature = 0 WHERE nid = " . $id . ";";
			} else {
				$query = "UPDATE book SET feature = 1 WHERE nid = " . $id . ";" ;
			}
            $query2 = "UPDATE entity SET entity.changed = NOW() WHERE nid = $id";
            
			$this->db->dbQuery($query);
            $this->db->dbQuery($query2);
			//$this->db->closeConnection();
		}
	}
	public function countBookById($id, $type)
	{
		if ($this->db != null) {
			//$this->db->startConnection();
			if ($type == 'Normal'){
				$query = "SELECT COUNT(b.nid) AS num
						  FROM book AS b, entity AS e, USER AS u, category AS c
						  WHERE c.cid = b.cid AND u.uid = e.uid AND e.nid = b.nid AND u.uid = $id AND e.status = 1 AND e.type = 'book'";
				$result = $this->db->dbQuery($query);
			} else {
				$query = "SELECT COUNT(b.nid) AS num
						  FROM book AS b, entity AS e, USER AS u, category AS c 
						  WHERE c.cid = b.cid AND u.uid = e.uid AND e.nid = b.nid AND e.status = 1 AND e.type = 'book' ";
				$result = $this->db->dbQuery($query);
			}
				
			if ($result != null) {
				$row = mysql_fetch_assoc($result);
				$num = $row['num'];
			} else {
				$num = 0;
			}
			return $num;
		}
	}

	public function getNow()
	{
		if ($this->db != null) {
			$this->db->startConnection();

			$result = $this->db->dbQuery("SELECT NOW() AS nowTime;");


			$row = mysql_fetch_assoc($result);
			$now = $row['nowTime'];
			$this->db->closeConnection();
			return $now;
		}
	}

	public function getMaxId()
	{
		//$this->db->startConnection();
		$query = "SELECT MAX(nid) AS nid FROM entity";
		$arrResult = $this->db->dbQuery($query);
		$row = mysql_fetch_assoc($arrResult);
		//$this->db->closeConnection();
		return $row['nid'];

	}

	public function getIdCategoryByName($name)
	{
		//$this->db->startConnection();
		$query = "SELECT cid
				  FROM category 
				  WHERE name = ". var_export($name, true);
		$arrResult = $this->db->dbQuery($query);
		$row = mysql_fetch_assoc($arrResult);
		//$this->db->closeConnection();
		return $row['cid'];
		//return $query;
	}

	public function getMaxWeight($uid){
		//$this->db->startConnection();
		$query = "SELECT MAX(weight) AS maxweight
				  FROM entity 
				  WHERE uid = $uid";
		$arrResult = $this->db->dbQuery($query);
		$result = mysql_fetch_assoc($arrResult);
		//$this->db->closeConnection();
		return $result['maxweight'];
	}

	public function getIdbookByName($name)
	{
		//$this->db->startConnection();
		$query = "SELECT nid
				  FROM entity 
				  WHERE entity.status = 1 AND entity.type = 'book' AND title = ". var_export($name, true);
		$arrResult = $this->db->dbQuery($query);
		$row = mysql_fetch_assoc($arrResult);
		//$this->db->closeConnection();
		return $row['nid'];
	}
    
	public function getBookById($idbook){
		//$this->db->startConnection();
		$query = "SELECT e.title, e.weight, b.summary, b.detail, c.name
                  FROM entity AS e, book AS b, category AS c 
                  WHERE b.nid = e.nid AND b.cid = c.cid AND b.nid = $idbook AND e.type = 'book' AND e.status = 1";
		$result = $this->db->getMixedData($query);
		//$this->db->closeConnection();
		return $result;
	}

	public function getRelatedBook($id)
	{
		//$this->db->startConnection();
		$query = "SELECT r.related
				  FROM related_book AS r, entity AS e, 
				  WHERE e.nid = $id AND e.type = 'book' AND e.status = 1";
		$result = $this->db->getMixedData($query);
		//$this->db->closeConnection();
		return $result;
	}

	/*get related book's name of book $id*/
	public function getRelatedBookName($id)
	{
		//$this->db->startConnection();
		$query = "SELECT e.title
				  FROM related_book AS r, entity AS e 
				  WHERE r.nid = $id AND e.type = 'book' AND r.related = e.nid";
		$result = $this->db->getMixedData($query);
		//$this->db->closeConnection();
		return $result;
	}

	// get newest bookId of user
	public function getBookByNewDate($uid)
	{
		//$this->db->startConnection();
		$query = "SELECT nid
				  FROM entity AS e 
				  WHERE e.type = 'book' AND e.status = 1 AND e.uid = $uid 
				  ORDER BY e.created DESC 
				  LIMIT 0, 1";
		$result = $this->db->getMixedData($query);
		$row = $result[0];
		//$this->db->closeConnection();
		return $row['nid'];

	}

	public function getBookByNewDate2($uid)
	{
		//$this->db->startConnection();
		$query = "SELECT nid
				  FROM entity AS e 
				  WHERE e.type = 'book' AND e.status = 1 AND e.uid = $uid 
				  ORDER BY e.changed DESC LIMIT 0, 1";
		$result = $this->db->getMixedData($query);
		$row = $result[0];
		//$this->db->closeConnection();
		return $row['nid'];

	}

	public function getRelatedId($nid)
	{

		$query = "SELECT related FROM related_book WHERE nid = $nid";
		$result = $this->db->getMixedData($query);


		return $result;

	}

	public function checkRelated($id1, $id2)
	{
		$query = "SELECT* FROM related_book WHERE (nid = $id1 AND related = $id2) OR (nid = $id2 AND related = $id1)";
		$result = $this->db->getMixedData($query);
		if (count($result) == 0){
			return false;
		} else {
			return true;
		}
	}

	public function delAllRelatedBook($id)
	{
		if ($this->db != null) {
			$this->db->dbQuery("DELETE FROM related_book WHERE nid = $id OR related = $id");
		}

	}

	public function getUserIdByBookId($id)
	{
		$query = "SELECT uid FROM entity WHERE nid = $id";
		$result = $this->db->getMixedData($query);
		$row  = $result[0];
		return $row['uid'];

	}

	public function getBookByCatIdLimit($catId, $fst, $num)
	{
		$query = "SELECT b.nid, e.title, c.name, u.username, e.created
				  FROM book AS b, entity AS e, category AS c, USER AS u 
				  WHERE u.uid = e.uid AND e.nid = b.nid AND b.cid = c.cid AND e.status = 1 AND c.cid = $catId AND e.type = 'book'
				  LIMIT $fst, $num";
		$result = $this->db->getMixedData($query);
		return $result;

	}

	public function countBookByCatId($cid)
	{
		$query = "SELECT COUNT(book.nid) AS num 
				  FROM book, entity 
				  WHERE cid = $cid AND entity.status = 1 AND book.nid = entity.nid AND entity.type = 'book' ;";
		$result = $this->db->getMixedData($query);
		$row = $result[0];
		// echo $row['num'];
		return $row['num'];
	}

	//-------------------------------------
	public function getRelatedbooks($id)
	{
		$startIndex = 0;
		$num = $this->db->get('configManager')->getById(3)->get("value"); //get related book page size

		$query = "SELECT *
				  FROM book, related_book, entity 
				  WHERE book.nid = related_book.nid AND book.nid = entity.nid AND related_book.related = $id AND entity.status = 1 AND entity.type = 'book' 
				  LIMIT $startIndex, $num";
		return $this->db->get_data($query, "book");
	}

	public function getSpecialBooks($startIndex = 0, $num = 0)
	{
		$query = "SELECT *
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND entity.status = 1 AND book.special = 1 AND entity.status = 1 AND entity.type = 'book' 
				  ORDER BY entity.changed DESC 
                  LIMIT $startIndex, $num ";		
		return $this->db->get_data($query, "book");
	}

	public function getAllCategoryBooks($startIndex = 0, $num = 0)
	{
		$query = "SELECT *
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND entity.status = 1 AND entity.type = 'book' 
				  ORDER BY entity.changed DESC
				  LIMIT $startIndex, $num";
		return $this->db->get_data($query, "book");
	}

	// Get the quantity of the special books
	public function getNumberOfSpecialBook()
	{
		$query = "SELECT COUNT(book.nid) AS num
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND book.special = 1 AND entity.status = 1";
		$arrResult = $this->db->getMixedData($query);
		$row = $arrResult[0];
		return $row['num'];
	}
	public function getPercent ($did){
		$query = "SELECT percent FROM discount WHERE did = $did ";
		$result = $this->db->getMixedData($query);
		$row = $result[0];
		return $row['percent'];
	}
	// Get the quantity of the books in one category
	public function getDiscountBooks($startIndex = 0, $num = 0){
		$query = "SELECT *
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND entity.status = 1 AND book.discount != 0 AND entity.status = 1 AND entity.type = 'book' 
				  ORDER BY entity.changed DESC 
                  LIMIT $startIndex, $num ";		
		return $this->db->get_data($query, "book");
	}
	public function getNumberOfDiscountBooks(){
		$query = "SELECT COUNT(book.nid) AS num
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND book.discount != 0 AND entity.status = 1";
		$arrResult = $this->db->getMixedData($query);
		$row = $arrResult[0];
		return $row['num'];
	}
	
	public function getNumberOfCategoryBook($cid){
		$query = "SELECT COUNT(nid) AS num
				  FROM book
				  WHERE cid = $cid";
		$arrResult = $this->db->getMixedData($query);
		$row = $arrResult[0];
		return $row['num'];
	}

	//Get the number of books
	public function getAllBooks(){
		$query = "SELECT COUNT(book.nid) AS num
				  FROM book, entity
				  WHERE book.nid = entity.nid AND entity.status = 1 ";
		$arrResult = $this->db->getMixedData($query);
		$row = $arrResult[0];
		return $row['num'];
	}

	public function getCategoryBooks($cid, $startIndex = 0, $num = 0)
	{
		$query = "SELECT *
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND entity.status = 1 AND book.cid = $cid AND entity.type = 'book' 
				  LIMIT $startIndex, $num";
		return $this->db->get_data($query, "book");
	}

	public function getById($id)
	{
		$query = "SELECT *
				  FROM book, entity 
				  WHERE book.nid = entity.nid AND entity.status = 1 AND book.nid = $id AND entity.type = 'book' ";
		$result = $this->db->get_data($query, "book");
		if (count($result) == 0) {
			return FALSE;
		} else {
			return $result[0];
		}
	}

	public function getFeature()
	{

		$query = "SELECT *
				  FROM entity, book 
				  WHERE entity.nid = book.nid AND book.feature = 1 AND entity.status = 1 AND entity.type = 'book'";
		$array = $this->db->get_data($query, 'book');
		return $array;
	}

	public function getFeatureLimit($startIndex, $num)
	{
		$query = "SELECT *
				  FROM entity, book 
				  WHERE entity.nid = book.nid AND book.feature = 1 AND entity.status = 1 AND entity.type = 'book'
				  ORDER BY entity.changed DESC
				  LIMIT $startIndex, $num";
		$array = $this->db->get_data($query, 'book');
		return $array;
	}

	public function getNew()
	{
		$query = "SELECT *
				  FROM entity JOIN book ON entity.nid = book.nid  
				  WHERE created >= DATE_ADD(NOW(), INTERVAL -7 DAY) AND entity.status = 1 AND entity.type = 'book'
				  ORDER BY entity.created DESC";
		$array = $this->db->get_data($query, 'book');
		return $array;
	}

	public function getNewLimit($startIndex, $num)
	{   
		$maxDate = $this->getMaxDate(); //edited by Khanh.Vu
		$query = "SELECT *
				  FROM entity JOIN book ON entity.nid = book.nid  
				  WHERE created >= DATE_ADD(" . var_export($maxDate, true) . ", INTERVAL -7 DAY) AND entity.status = 1 AND entity.type = 'book'
				  ORDER BY entity.created DESC
				  LIMIT $startIndex, $num";
		$array = $this->db->get_data($query, 'book');
		return $array;
	}
	
    public function getMaxDate(){
        $query = 'SELECT MAX(created) AS maxdate FROM entity WHERE entity.type = "book"';
        $result = $this->db->getMixedData($query);
        $row = $result[0];
        return $row['maxdate'];
    }
    public function getNewLimitByUser($startIndex, $num)
	{
		$query = "SELECT *
				  FROM entity, book, user    
				  WHERE entity.nid = book.nid AND user.uid = entity.uid AND created >= DATE_ADD(NOW(), INTERVAL -7 DAY) AND entity.status = 1 AND entity.type = 'book'
				  ORDER BY entity.created DESC
				  LIMIT $startIndex, $num";
		$array = $this->db->get_data($query, 'book');
		return $array;
	}
    
	public function setStatusById($id, $st)
	{
		$query = "UPDATE entity
				  SET status = $st
				  WHERE nid = $id";
		$this->db->dbQuery($query);
	}

	public function getFieldBook($idBook){
		//$this->registry->db->startConnection();
		$query = "SELECT *
				  FROM book 
				  WHERE nid = $idBook";
		$result = $this->db->getMixedData($query);
		//$this->registry->db->closeConnection();
		return $result;
	}

	public function getAllBookNames($user)
	{	
		if ($user->get('type') == 'Admin') {
			$u = 'u.username LIKE "%"';
		} 
		else {
			$u = "u.username = " . var_export($user->get('username'), true);
		}
		$query = "SELECT DISTINCT e.title AS `name`
				  FROM book AS b, entity AS e, `user` AS u
				  WHERE b.nid = e.nid AND e.type = 'book' AND e.status = 1 AND u.uid = e.uid AND $u";		
		$result = $this->db->getMixedData($query);
		return $result;
	}		
	
	public function getFilterBooksLimit($bname, $cat, $user, $fst, $num)
	{
		//$bname = addslashes(mysql_real_escape_string($bname));
		//$cat = addslashes(mysql_real_escape_string($cat));
		//$user = addslashes(mysql_real_escape_string($user));
		
		if ($bname == NULL ) {
			$bn = 'e.title LIKE "%"';
		} 
		else {
			$bn = "e.title = " . '"' . $bname . '"';
			//echo var_export($bname, true);
		}		
		if ($cat == NULL ) {
			$c = 'c.name LIKE "%"';
		} 
		else {
			$c = "c.name = " . '"' . $cat . '"';
		}
		if ($user == NULL ) {
			$u = 'u.username LIKE "%"';
		} 
		else {
			$u = "u.username = " . '"' . $user . '"';
		}
		$query = "SELECT b.nid, e.title, c.name, u.username, e.created, b.feature, b.special
				  FROM entity AS e, book AS b, `user` AS u, category AS c
				  WHERE e.nid = b.nid AND b.cid = c.cid AND e.uid = u.uid AND e.status = 1 AND
				  	    $bn AND $c AND $u  
				  LIMIT $fst, $num 
				 ";
		
		$result = $this->db->getMixedData($query);
		
		return $result;				  	    
	}
	
	public function countFilterBooks($bname, $cat, $user) {
		$bname = mysql_real_escape_string($bname);
		$cat = mysql_real_escape_string($cat);
		$user = mysql_real_escape_string($user);
		if ($bname == NULL ) {
			$bn = 'e.title LIKE "%"';
		} 
		else {
			$bn = "e.title = " . var_export($bname, true);
		}		
		if ($cat == NULL ) {
			$c = 'c.name LIKE "%"';
		} 
		else {
			$c = "c.name = " . var_export($cat, true);
		}
		if ($user == NULL ) {
			$u = 'u.username LIKE "%"';
		} 
		else {
			$u = "u.username = " . var_export($user, true);
		}
		
		$query = "SELECT COUNT(b.nid) AS num
				  FROM entity AS e, book AS b, `user` AS u, category AS c
				  WHERE e.nid = b.nid AND b.cid = c.cid AND e.uid = u.uid AND e.status = 1 AND
				  	    $bn AND $c AND $u  				  
				 ";
		$result = $this->db->dbQuery($query);
		if ($result != null) {
			$row = mysql_fetch_assoc($result);
			$num = $row['num'];
		} else {
			$num = 0;
		}
		return $num;				  	    				  	   
	}
	
    public function getBookId(){
        $query = "SELECT nid FROM book";
        $result = $this->db->getMixedData($query);
        return $result;
    }
    public function getFamousBook(){
        $query = "SELECT p.like, p.share, p.retweet,b.point, e.title, u.username, b.thumbnail, b.nid FROM plugin AS p, book AS b, entity AS e, user AS u, (SELECT nid, MAX(date) AS date FROM plugin GROUP BY nid) AS plMax WHERE u.uid = e.uid AND e.nid = b.nid AND b.nid = p.nid AND p.nid = plMax.nid AND p.date = plMax.date ORDER BY b.point DESC LIMIT 0, 20";
        $result = $this->db->getMixedData($query);
        return $result;
    }
    function getLSRByIdLimit($nid, $from, $to)
    {
        $query = "SELECT DATE_FORMAT(p.date, '%d-%m-%y') AS date, p.like, p.share, p.retweet 
                 FROM PLUGIN AS p 
                 WHERE (
                 (p.date >= STR_TO_DATE('$from', '%d-%b-%Y')) 
                 AND 
                 (p.date < (DATE_ADD(STR_TO_DATE('$to', '%d-%b-%Y'), INTERVAL 1 DAY)))
                 AND
                 (p.nid = $nid)
                 )";
        $tblResult = $this->db->getMixedData($query);
        return $tblResult;
    }
    
    function countBooksUsers($from, $to)
    {
        if (strtotime($to)>strtotime(date("d-M-Y"))){
            $to = date("d-M-Y");
        }      
        $query = "SELECT DATEDIFF(STR_TO_DATE('$to', '%d-%b-%Y'), STR_TO_DATE('$from', '%d-%b-%Y')) AS diffDay;";
        $result = $this->db->getMixedData($query);
		$row = $result[0];
		$count =  $row['diffDay'] + 1;
        $offset = floor($count/7);
        if ($offset == 0){
            $offset = 1;
        }
        if ($count >= 7){
            $count = 7;
            }
         
        for ($i = 0; $i< $count; $i++){
                $numDate = $offset*$i + 1;
                $query2 = "SELECT COUNT(book.nid) AS num
                           FROM entity JOIN book ON entity.nid = book.nid
                           WHERE TYPE ='book' AND created<(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL $numDate DAY))";
                $query3 = "SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL ". $offset*$i ." DAY), '%d-%m-%y') AS showDate";
                $query4 = "SELECT COUNT(uid) AS num
                           FROM USER 
                           WHERE datejoin<(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL $numDate DAY))";
               
                $r2 = $this->db->getMixedData($query2);
                $r3 = $this->db->getMixedData($query3);
                $r4 = $this->db->getMixedData($query4);
                $row2 = $r2[0];
                $row3 = $r3[0];
                $row4 = $r4[0];
               // echo var_export($row3, true);
                $resultArr[$i] = array('date' => $row3['showDate'],
                                        'book' => $row2['num'],
                                        'user' => $row4['num']);
            }                    
               
       
        return $resultArr;
    }
    
    function getTotalLSR($from, $to)
    {
        $queryTo = "SELECT SUM(p.like) AS sumLike, SUM(p.share) AS sumShare, SUM(p.retweet) AS sumRetweet 
                  FROM PLUGIN AS p
                  WHERE p.date>= STR_TO_DATE('$to', '%d-%b-%Y') AND p.date < (DATE_ADD(STR_TO_DATE('$to', '%d-%b-%Y'), INTERVAL 1 DAY))";
        $resultTo = $this->db->getMixedData($queryTo);
        $rowTo = $resultTo[0];
        $queryFrom = "SELECT SUM(p.like) AS sumLike, SUM(p.share) AS sumShare, SUM(p.retweet) AS sumRetweet 
                  FROM PLUGIN AS p
                  WHERE p.date>= STR_TO_DATE('$from', '%d-%b-%Y') AND p.date < (DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL 1 DAY))";
        $resultFrom = $this->db->getMixedData($queryFrom);
        $rowFrom = $resultFrom[0];
        
        if ($rowFrom['sumLike'] == NULL){
            $rowFrom['sumLike'] = 0;
            $rowFrom['sumShare'] = 0;
            $rowFrom['sumRetweet'] = 0;
        }
        if ($rowTo['sumLike'] == ''){
            return 'noData';
        }
        $res['sumLike'] = $rowTo['sumLike'] - $rowFrom['sumLike'];
        $res['sumShare'] = $rowTo['sumShare'] - $rowFrom['sumShare'];
        $res['sumRetweet'] = $rowTo['sumRetweet'] - $rowFrom['sumRetweet'];
        return $res;
    }
    function getTotalBook($to)
    {
        $query = "SELECT COUNT(b.nid) AS num
						  FROM book AS b JOIN entity AS e ON e.nid = b.nid
						  WHERE e.status = 1 AND created<(DATE_ADD(STR_TO_DATE('$to', '%d-%b-%Y'), INTERVAL 1 DAY))";
        $result = $this->db->getMixedData($query);
        $row = $result[0];
        return $row['num'];
    }
	function getTitleBook($cid){
		$query = "SELECT * from entity Where nid = $cid ;"; 
        $result = $this->db->getMixedData($query);
		$row = $result[0];
		return $row['title'];
	}
	function getTotalByDate($from, $to){
		$query = "SELECT sum(p.like) as sumLike, sum(p.share) as sumShare , sum(p.retweet) as sumRetweet, DATE_FORMAT(p.date,'%d-%b-%Y') as date 
																from plugin as p 
																WHERE ((p.date >= STR_TO_DATE('$from', '%d-%b-%Y')) 
																AND 
																(p.date <= STR_TO_DATE('$to', '%d-%b-%Y'))) 
																group by DATE_FORMAT(p.date,'%d-%b-%Y')"; 
        $result = $this->db->getMixedData($query);
		return $result;
	}
	function countBookUserTable($from, $to){
	   if (strtotime($to)>strtotime(date("d-M-Y"))){
            $to = date("d-M-Y");
        }
        $query = "SELECT DATEDIFF(STR_TO_DATE('$to', '%d-%b-%Y'), STR_TO_DATE('$from', '%d-%b-%Y')) AS diffDay;";
        $result = $this->db->getMixedData($query);
		$row = $result[0];
		$count =  $row['diffDay'] + 1;
        for ($i = 0; $i< $count; $i++){
                $query2 = "SELECT COUNT(book.nid) AS num
                           FROM entity JOIN book ON entity.nid = book.nid
                           WHERE TYPE ='book' AND created<(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL $i DAY))";
                $query3 = "SELECT DATE_FORMAT(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL ". $i ." DAY),'%d-%b-%Y') AS showDate";
                $query4 = "SELECT COUNT(uid) AS num
                           FROM USER 
                           WHERE datejoin<(DATE_ADD(STR_TO_DATE('$from', '%d-%b-%Y'), INTERVAL $i DAY))";
               
                $r2 = $this->db->getMixedData($query2);
                $r3 = $this->db->getMixedData($query3);
                $r4 = $this->db->getMixedData($query4);
                $row2 = $r2[0];
                $row3 = $r3[0];
                $row4 = $r4[0];
               // echo var_export($row3, true);
                $resultArr[$i] = array('date' => $row3['showDate'],
                                        'book' => $row2['num'],
                                        'user' => $row4['num']);
            }                    
               
       
        return $resultArr;
	}
    
    function setPointBook()
    {
        $query = "SELECT b.nid, e.title, ROUND(b.point/(SELECT MAX(POINT) FROM book), 2) AS point FROM entity AS e, book AS b WHERE e.nid = b.nid";
        $result = $this->db->getMixedData($query);
		return $result;
    }
    
}

?>